class SubmissionController < ApplicationController
  before_filter :login_required, :except => ['run_cron']

  def index
    list
    render :action => 'list'
  end

  # GETs should be safe (see http://www.w3.org/2001/tag/doc/whenToUseGet.html)
  verify :method => :post, :only => [ :destroy, :create, :update ],
         :redirect_to => { :action => :list }

  def list
    # removed because it takes too much memory
    #Submission.mark_approved_and_completed  
    # # get all of the submissions that have judgments
    # @submissions = Submission.find_by_sql("select submissions.*, avg(judgments.score) as a from submissions join judgments on (submissions.id = judgments.submission_id) group by submissions.id order by a desc")
    # @submissions += Submission.find_by_sql("select submissions.* from submissions left join judgments on (submissions.id = judgments.submission_id) where judgments.id is null")
  end  
  
  def filter
    @cycle = "All"
    @status = "All"
    @conn = ActiveRecord::Base.connection()
    sql = "SELECT s.id as id,
             granted,
             judges_avg_score,
             total_cost,
             c.name as cycle_name,
             u.id as user_id, 
             u.first_name as user_first_name, 
             u.last_name as user_last_name,
             statuses.name as status_name,
             judge_completed_count,
             judge_total_count,
             approval_completed_count,
             approval_total_count
             FROM submissions s
             JOIN cycles c ON (s.cycle_id = c.id)
             JOIN users u ON (s.user_id = u.id)
             JOIN statuses ON (s.status_id = statuses.id)
             LEFT JOIN (select submission_id as id, count(*) as judge_completed_count from judgments where complete = 1 group by submission_id) jcc ON (jcc.id = s.id)
             LEFT JOIN (select submission_id as id, count(*) as judge_total_count from judgments group by submission_id) jc ON (jc.id = s.id)
             LEFT JOIN (select submission_id as id, count(*) as approval_completed_count from approvals where complete = 1 group by submission_id) acc ON (acc.id = s.id)
             LEFT JOIN (select submission_id as id, count(*) as approval_total_count from approvals group by submission_id) ac ON (ac.id = s.id)
           WHERE 1=1 "

    if params[:filter].key?("cycle") and params[:filter]['cycle'] != ""
      @cycle = params[:filter]['cycle']
    end
    
    if params[:filter].key?("status") and params[:filter]['status'] != ""
      @status = params[:filter]['status']
    end
                                                       
    if @status == 'All' and @cycle == 'All'
       @rows = []
    elsif @status == 'All'
      sql += "AND cycle_id = #{@conn.quote(@cycle)} "
      sql += "ORDER BY judges_avg_score desc"
    elsif @cycle == 'All' 
      @rows = []
    else
      sql += "AND cycle_id = #{@conn.quote(@cycle)} "
      sql += "AND status_id = #{@conn.quote(@status)} "
      sql += "ORDER BY judges_avg_score desc"
    end                                                 

    @rows = @conn.select_all(sql)
    render :partial => 'list_table'
  end

  def show
    @submission = Submission.find(params[:id]) 
    
    if @submission.data
      @submission_data = HashModel::Model.new(YAML::load(@submission.data))
    else    
      @submission_data = HashModel::Model.new({})
    end
  end

  def new
    @submission = Submission.new
  end

  def edit
    @submission = Submission.find(params[:id])
  end

  def update
    @submission = Submission.find(params[:id])
    submission_attr = params[:submission] 
    
    if params[:submission_data]
      submission_attr['data'] = YAML::dump(params[:submission_data])
    end
    
    if @submission.update_attributes(submission_attr)
      
      flash[:notice] = 'Submission was successfully updated.'
      redirect_to :action => 'show', :id => @submission
    else
      render :action => 'edit'
    end
  end
  
  def assign_judge
    @submission = Submission.find(params[:id])
    @judgments = Judgment.find(:all, :conditions => ["submission_id = ?", @submission], :include => [:judge])
    return unless request.post? 
    site = params[:site]   
    
    if site.has_key?("judge_id") and site['judge_id'] != "" 
      
      # look to see if that judge has already been assigned
      for judgment in @judgments
        if judgment.judge.id.to_i == site['judge_id'].to_i
          return
        end
      end 
      j = Judge.find(site['judge_id'])
      @submission.judgments << Judgment.create(:judge => j, :score => 0)
      UserNotifier.deliver_notify_judges(j)
      @submission.status = Status.find_or_create_by_name("Judging")
      @submission.save!
      flash[:notice] = 'Judge was assigned successfully and an email notification has been sent.'
      @judgments = Judgment.find(:all, :conditions => ["submission_id = ?", @submission], :include => [:judge])
    end     
  end   
  
  def destroy_judgment   
    if current_user.is_admin? || current_user.is_adminasst?
      judgment = Judgment.find(params['id'])
      submission_id = judgment.submission_id
      judgment.destroy
    end                                  
    redirect_to :action => "assign_judge", :id => submission_id
  end
    
  
  def grant
    @submission = Submission.find(params[:id])
    @submission.update_attribute(:granted, true)
    render :text => 'Granted'    
  end 
  
  def deny
    @submission = Submission.find(params[:id]) 
    @submission.update_attribute(:granted, false)
    render :text => 'Denied'    
  end

  def destroy     
    logger.debug "Current User: #{current_user.display_name}"
    if current_user.is_admin?
      logger.info "#{current_user.display_name} (#{current_user.id}) has deleted submission #{params[:id]}"
      Submission.find(params[:id]).destroy 
    end
    redirect_to :action => 'list'
  end 
  
  def run_cron
    Submission.mark_completed 
    Submission.mark_approved 
    Submission.mark_not_approved 
    render :text => "complete"
  end   
  
  def money_spent  
    
    if params[:id] 
      cycle_id = params[:id]
    else
      render :nothing => true
      return
    end
    
    cycle = Cycle.find(cycle_id)
    total_sum =  cycle.budget_amount  || 0
    granted_sum = Submission.sum('total_cost', :conditions => ["cycle_id = ? and granted = true", cycle_id])  || 0
    
    if granted_sum <= total_sum     
      render :text => sprintf("<p style=\"color: green\">$%0.2f of $%0.2f allocated for grant cycle %s.</p>", granted_sum, total_sum, cycle.name)                    
    else 
      render :text => sprintf("<p style=\"color: red\">$%0.2f of $%0.2f allocated for grant cycle %s.</p>", granted_sum, total_sum, cycle.name)
    end
  end
end
